IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_LineaTiempo _Proyecto]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [LineaTiempo] DROP CONSTRAINT [FK_LineaTiempo _Proyecto]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_LineaTiempo _Recurso]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [LineaTiempo] DROP CONSTRAINT [FK_LineaTiempo _Recurso]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_Proyecto_Estatus]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [Proyecto] DROP CONSTRAINT [FK_Proyecto_Estatus]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_ProyectoArea_Area]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [ProyectoArea] DROP CONSTRAINT [FK_ProyectoArea_Area]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_ProyectoArea_Proyecto]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [ProyectoArea] DROP CONSTRAINT [FK_ProyectoArea_Proyecto]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_Recurso_TipoRecurso]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [Recurso] DROP CONSTRAINT [FK_Recurso_TipoRecurso]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_TecnologiaProyecto_Proyecto]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [TecnologiaProyecto] DROP CONSTRAINT [FK_TecnologiaProyecto_Proyecto]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_TecnologiaProyecto_Tecnologia]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [TecnologiaProyecto] DROP CONSTRAINT [FK_TecnologiaProyecto_Tecnologia]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_TecnologiaRecurso_Recurso]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [TecnologiaRecurso] DROP CONSTRAINT [FK_TecnologiaRecurso_Recurso]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[FK_TecnologiaRecurso_Tecnologia]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE [TecnologiaRecurso] DROP CONSTRAINT [FK_TecnologiaRecurso_Tecnologia]
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[Area]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [Area]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[Estatus]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [Estatus]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[LineaTiempo]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [LineaTiempo]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[Proyecto]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [Proyecto]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[ProyectoArea]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [ProyectoArea]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[Recurso]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [Recurso]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[Tecnologia]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [Tecnologia]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[TecnologiaProyecto]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [TecnologiaProyecto]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[TecnologiaRecurso]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [TecnologiaRecurso]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[TipoRecurso]') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [TipoRecurso]
GO


CREATE TABLE [Area] ( 
	[id] int identity(1,1)  NOT NULL,
	[nombre] nvarchar(100) NOT NULL,
	[Activo] bit NOT NULL
)
GO

CREATE TABLE [Estatus] ( 
	[id] int identity(1,1)  NOT NULL,
	[descripcion] nvarchar(50)
)
GO

CREATE TABLE [LineaTiempo] ( 
	[id] int identity(1,1)  NOT NULL,
	[recursoId] int,
	[proyectoId] int,
	[fechaInicio] datetime,
	[fechaFinal] datetime
)
GO

CREATE TABLE [Proyecto] ( 
	[id] int identity(1,1)  NOT NULL,
	[nombre] nvarchar(200),
	[fechaInicio] datetime,
	[fechaFin] datetime,
	[horas] int,
	[activo] bit,
	[estatusId] int
)
GO

CREATE TABLE [ProyectoArea] ( 
	[areaId] int NOT NULL,
	[proyectoId] int NOT NULL
)
GO

CREATE TABLE [Recurso] ( 
	[id] int identity(1,1)  NOT NULL,
	[nombre] nvarchar(200),
	[apellidoMaterno] nvarchar(200),
	[apellidoPaterno] nvarchar(200),
	[fechaNacimiento] datetime,
	[tipoRecursoId] int,
	[telefono] nvarchar(20),
	[Activo] bit
)
GO

CREATE TABLE [Tecnologia] ( 
	[id] int identity(1,1)  NOT NULL,
	[nombre] nvarchar(100) NOT NULL,
	[Activo] bit
)
GO

CREATE TABLE [TecnologiaProyecto] ( 
	[proyectoid] int NOT NULL,
	[tecnologiaId] int NOT NULL
)
GO

CREATE TABLE [TecnologiaRecurso] ( 
	[tecnologiaId] int,
	[recursoId] int
)
GO

CREATE TABLE [TipoRecurso] ( 
	[id] int identity(1,1)  NOT NULL,
	[descripcion] nvarchar(50),
	[costo] decimal(10,2),
	[activo] bit
)
GO


ALTER TABLE [Area] ADD CONSTRAINT [PK_Area] 
	PRIMARY KEY CLUSTERED ([id])
GO

ALTER TABLE [Estatus] ADD CONSTRAINT [PK_Estatus] 
	PRIMARY KEY CLUSTERED ([id])
GO

ALTER TABLE [LineaTiempo] ADD CONSTRAINT [PK_LineaTiempo ] 
	PRIMARY KEY CLUSTERED ([id])
GO

ALTER TABLE [Proyecto] ADD CONSTRAINT [PK_Proyecto] 
	PRIMARY KEY CLUSTERED ([id])
GO

ALTER TABLE [Recurso] ADD CONSTRAINT [PK_Recurso] 
	PRIMARY KEY CLUSTERED ([id])
GO

ALTER TABLE [Tecnologia] ADD CONSTRAINT [PK_Tecnologia] 
	PRIMARY KEY CLUSTERED ([id])
GO

ALTER TABLE [TipoRecurso] ADD CONSTRAINT [PK_TipoRecurso] 
	PRIMARY KEY CLUSTERED ([id])
GO



ALTER TABLE [LineaTiempo] ADD CONSTRAINT [FK_LineaTiempo _Proyecto] 
	FOREIGN KEY ([proyectoId]) REFERENCES [Proyecto] ([id])
GO

ALTER TABLE [LineaTiempo] ADD CONSTRAINT [FK_LineaTiempo _Recurso] 
	FOREIGN KEY ([recursoId]) REFERENCES [Recurso] ([id])
GO

ALTER TABLE [Proyecto] ADD CONSTRAINT [FK_Proyecto_Estatus] 
	FOREIGN KEY ([estatusId]) REFERENCES [Estatus] ([id])
GO

ALTER TABLE [ProyectoArea] ADD CONSTRAINT [FK_ProyectoArea_Area] 
	FOREIGN KEY ([areaId]) REFERENCES [Area] ([id])
GO

ALTER TABLE [ProyectoArea] ADD CONSTRAINT [FK_ProyectoArea_Proyecto] 
	FOREIGN KEY ([proyectoId]) REFERENCES [Proyecto] ([id])
GO

ALTER TABLE [Recurso] ADD CONSTRAINT [FK_Recurso_TipoRecurso] 
	FOREIGN KEY ([tipoRecursoId]) REFERENCES [TipoRecurso] ([id])
GO

ALTER TABLE [TecnologiaProyecto] ADD CONSTRAINT [FK_TecnologiaProyecto_Proyecto] 
	FOREIGN KEY ([proyectoid]) REFERENCES [Proyecto] ([id])
GO

ALTER TABLE [TecnologiaProyecto] ADD CONSTRAINT [FK_TecnologiaProyecto_Tecnologia] 
	FOREIGN KEY ([tecnologiaId]) REFERENCES [Tecnologia] ([id])
GO

ALTER TABLE [TecnologiaRecurso] ADD CONSTRAINT [FK_TecnologiaRecurso_Recurso] 
	FOREIGN KEY ([recursoId]) REFERENCES [Recurso] ([id])
GO

ALTER TABLE [TecnologiaRecurso] ADD CONSTRAINT [FK_TecnologiaRecurso_Tecnologia] 
	FOREIGN KEY ([tecnologiaId]) REFERENCES [Tecnologia] ([id])
GO
